# -*- coding:utf-8 -*-
import pymysql
from utils.config.readConfig import ReadConfig

localReadConfig = ReadConfig()


class MyDB:
    # dbname连接config中不同的数据库
    def __init__(self, dbname):
        self.db = None
        self.cursor = None
        self.dbname = dbname
        self.host = localReadConfig.get_db(self.dbname, "host")
        self.username = localReadConfig.get_db(self.dbname, "username")
        self.password = localReadConfig.get_db(self.dbname, "password")
        self.port = localReadConfig.get_db(self.dbname, "port")
        self.database = localReadConfig.get_db(self.dbname, "database")
        self.config = {
            'host': str(self.host),
            'user': self.username,
            'passwd': self.password,
            'port': int(self.port),
            'db': self.database
        }

    def connectDB(self):
        """
        connect to database
        :return:
        """
        try:
            # connect to DB
            self.db = pymysql.connect(charset='utf8', **self.config)
            # create cursor
            self.cursor = self.db.cursor()
            print("Connect DB successfully! - " + self.host)
        except Exception as ex:
            print(ex)

    def executeSQL(self, sql, *params):
        """
        execute sql
        执行sql语句
        :param sql:
        :return:
        """
        self.connectDB()
        # executing sql
        self.cursor.execute(sql, *params)
        # executing by committing to DB
        self.db.commit()
        self.closeDB()
        return self.cursor

    def get_all(self, cursor):
        """
        get all result after execute sql
        :param cursor:
        :return:
        """
        value = cursor.fetchall()
        return value

    def get_one(self, cursor):
        """
        get one result after execute sql
        :param cursor:
        :return:
        """
        value = cursor.fetchone()
        return value

    def get_all_value(self, sql, param):
        return self.get_all(self.executeSQL(sql, param))

    def get_field_value(self, sql, param):
        """
        获取返回的唯一值
        :param sql:
        :param param:
        :return:
        """
        cursor = self.executeSQL(sql, param)
        getvalue = self.get_one(cursor)
        return getvalue[0]

    def closeDB(self):
        """
        close database
        :return:
        """
        self.db.close()
        # print("Database closed!")


from DBUtils.PooledDB import PooledDB, SharedDBConnection

'''
连接池
'''


class MysqlPool(object):

    def __init__(self, xhost, xport, xuser, xpassword, xdatabase):
        self.POOL = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
            maxshared=3,
            # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host=xhost,
            port=xport,
            user=xuser,
            password=xpassword,
            # database=xdatabase,
            charset='utf8'
        )

    # def __new__(cls, *args, **kw):
    #     '''
    #     启用单例模式
    #     :param args:
    #     :param kw:
    #     :return:
    #     '''
    #     if not hasattr(cls, '_instance'):
    #         cls._instance = object.__new__(cls)
    #     return cls._instance

    def connect(self):
        '''
        启动连接
        :return:
        '''
        conn = self.POOL.connection()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        return conn, cursor

    def connect_close(self, conn, cursor):
        '''
        关闭连接
        :param conn:
        :param cursor:
        :return:
        '''
        cursor.close()
        conn.close()

    def fetch_all(self, sql, args):
        '''
        批量查询
        :param sql:
        :param args:
        :return:
        '''
        conn, cursor = self.connect()

        cursor.execute(sql, args)
        record_list = cursor.fetchall()
        self.connect_close(conn, cursor)

        return record_list

    def fetch_one(self, sql, args):
        '''
        查询单条数据
        :param sql:
        :param args:
        :return:
        '''
        conn, cursor = self.connect()
        cursor.execute(sql, args)
        result = cursor.fetchone()
        self.connect_close(conn, cursor)

        return result

    def insert(self, sql, args):
        '''
        插入数据
        :param sql:
        :param args:
        :return:
        '''
        conn, cursor = self.connect()
        row = cursor.execute(sql, args)
        conn.commit()
        self.connect_close(conn, cursor)
        return row


class MyDBPool:
    def __init__(self, dbname):
        self.dbname = dbname
        self.host = localReadConfig.get_db(self.dbname, "host")
        self.username = localReadConfig.get_db(self.dbname, "username")
        self.password = localReadConfig.get_db(self.dbname, "password")
        self.port = int(localReadConfig.get_db(self.dbname, "port"))
        self.database = localReadConfig.get_db(self.dbname, "database")

        self.dbpool = MysqlPool(self.host,
                                self.port,
                                self.username,
                                self.password,
                                self.database)

    def fetchAll(self, sql, args):
        return self.dbpool.fetch_all(sql, args)

    def fetchOne(self, sql, args):
        return self.dbpool.fetch_one(sql, args)

    def modify(self, sql, args):
        return self.dbpool.insert(sql, args)


if __name__ == "__main__":
    DBtest = MyDB(dbname="DATABASE")
    obligorid = 42949883522
    sql = "SELECT AssetCode FROM p2p_aum.loaner_repayment WHERE UserId=42949883522;"
    cursor = DBtest.executeSQL(sql)
    getvalue = DBtest.get_one(cursor)
    print(getvalue)
